﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Threading.Tasks;
using DevExpress.XtraEditors;

namespace BuildHelper.Work.Tools
{
    public partial class DBUtilsForm : XtraForm
    {
        public DBUtilsForm()
        {
            InitializeComponent();
            dataSetDbUtils.EnforceConstraints = false;
        }
        #region ResMan
        string gcmResx;
        string dbsResx;

        DataSets.DataSetDbUtils dsTmpTables = new DataSets.DataSetDbUtils();
        ProgNetComponentsDx.Forms.FormBackGroundWorkSingleThread resManForm;

        List<string> tables = new List<string>();
        List<string> columns = new List<string>();
        List<string> resx = new List<string>();

        private void btnResManSprawdz_Click(object sender, EventArgs e)
        {
            string brancz = resManControlResMan.Brancz;
            if (brancz.IsNull())
            {
                this.ShowDxInfoDialog("Wybierz brancza");
                return;
            }
            dataSetDbUtils.ResMan.Clear();
            dsTmpTables.ResMan.Clear();

            tables.Clear();
            columns.Clear();

            if(radioGroupResManSource.EditValue.AsString() == "W")
            {
                if (ceResManKolumny.Checked)
                {
                    foreach (DataRow r in dataSetDbUtils.USER_TAB_COLS)
                        columns.AddIfNotExist(r["COLUMN_NAME"].AsString());
                }
                if (ceResManTabele.Checked)
                {
                    foreach (DataRow r in dataSetDbUtils.USER_TABLES)
                        tables.AddIfNotExist(r["TABLE_NAME"].AsString());
                }
            }
            else if (radioGroupResManSource.EditValue.AsString() == "T")
            {
                string table = teResManTabela.EditValue.AsString();
                if (ceResManKolumny.Checked)
                {
                    foreach (DataRow r in dataSetDbUtils.USER_TAB_COLS.Select("TABLE_NAME = " + table.EscapeSql()))
                        columns.AddIfNotExist(r["COLUMN_NAME"].AsString());
                }
                if (ceResManTabele.Checked)
                {
                    tables.AddIfNotExist(table);
                }
            }
            else if (radioGroupResManSource.EditValue.AsString() == "R")
            {
                string table = teResManTabela.EditValue.AsString();
                AddTable(table, tables, columns);
            }
            if (!ceResManTabele.Checked)
                tables.Clear();
            gcmResx = resManControlResMan.GetGcmResManFile(brancz).ReadFile();
            dbsResx = resManControlResMan.GetDBsResManFile(brancz).ReadFile();
            resx.Clear();
            resx.AddRange(gcmResx.GetMatches("<data name\\s*=\\s*\"([^\"]*)\""));
            resx.AddRange(dbsResx.GetMatches("<data name\\s*=\\s*\"([^\"]*)\""));

            resManForm = new ProgNetComponentsDx.Forms.FormBackGroundWorkSingleThread();
            resManForm.DoWork +=new DoWorkEventHandler(resManForm_DoWork);
            resManForm.RunWorkerCompleted += new RunWorkerCompletedEventHandler(resManForm_RunWorkerCompleted);
            resManForm.ShowDialog();
        }

        void resManForm_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            dataSetDbUtils.ResMan.Merge(dsTmpTables.ResMan);
        }

        void resManForm_DoWork(object sender, DoWorkEventArgs e)
        {
            resManForm.BackgroundControl.MaxProgress = tables.Count + columns.Count;
            resManForm.BackgroundControl.SupportCancelation = true;
            foreach (string s in tables)
            {
                if (resManForm.BackgroundControl.IsCanceling)
                    return;
                CheckString(s);
            }
            foreach (string s in columns)
            {
                if (resManForm.BackgroundControl.IsCanceling)
                    return;
                CheckString(s);
            }
        }

        private void CheckString(string s)
        {
         //   if (!dbsResx.XMatch("<data name\\s*=\\s*" + s.AddDoubleQuotes())
                    //&& !gcmResx.XMatch("<data name\\s*=\\s*" + s.AddDoubleQuotes()))
            if(!resx.Contains(s))
            {
                AddStr(s);
            }
            resManForm.BackgroundControl.Progress++;
        }

        private void AddStr(string s)
        {
            if (this.InvokeRequired)
            {
                this.Invoke(new Action<String>(AddStr), s);
                return;
            }
            dsTmpTables.ResMan.AddResManRow(s, "");
        }
        private void AddTable(string table, List<string> tables, List<string> columns)
        {
            if (!tables.Contains(table))
            {
                tables.Add(table);
                if (ceResManKolumny.Checked)
                {
                    foreach (DataRow r in dataSetDbUtils.USER_TAB_COLS.Select("TABLE_NAME = " + table.EscapeSql()))
                        columns.AddIfNotExist(r["COLUMN_NAME"].AsString());
                }
                DataRow[] refs = dataSetDbUtils.REFERENCES.Select("TABLE_NAME = " + table.EscapeSql());
                foreach (DataRow r in refs)
                {
                        AddTable(r["PK_TABLE"].AsString(), tables, columns);
                }
                refs = dataSetDbUtils.REFERENCES.Select("PK_TABLE = " + table.EscapeSql());
                foreach (DataRow r in refs)
                {
                    if (isChildRel(r))
                        AddTable(r["TABLE_NAME"].AsString(), tables, columns);
                }
            }
        }
        private void btnResManClear_Click(object sender, EventArgs e)
        {
            teResManTabela.EditValue = "";
            dataSetDbUtils.ResMan.Clear();
            dataSetDbUtils.ResMan.AcceptChanges();
            resManControlResMan.Text = "";
        }
        private void btnResManGenerate_Click(object sender, EventArgs e)
        {
            resManControlResMan.Text = "";
            dataSetDbUtils.ResMan.ClearErrors();

            DataRow[] rows = dataSetDbUtils.ResMan.Select(null, null, DataViewRowState.CurrentRows);
            bool ok = true;
            foreach (DataRow r in rows)
            {
                if (r["KOD"].IsNull())
                {
                    ok = false;
                    r.SetColumnError("KOD", "Ta wartość jest wymagana");
                }
                if (r["NAZWA"].IsNull())
                {
                    ok = false;
                    r.SetColumnError("NAZWA", "Ta wartość jest wymagana");
                }
            }
            if (!ok)
            {
                DialogResult dlg = this.ShowDxYNQuestionDialog("Nie wszystkie wartości są podane.\nCzy chcesz dodać tylko uzupełnione wpisy?") ;
                ok = dlg == System.Windows.Forms.DialogResult.Yes;
            }
            if (ok)
            {
                foreach (DataRow r in rows)
                {
                    if (r["KOD"].IsNotNull() && r["NAZWA"].IsNotNull())
                    {
                        resManControlResMan.AppendText(r["KOD"].AsString(), r["NAZWA"].AsString());
                    }
                }
            }
        }
        private void btnResManUzupelnij_Click(object sender, EventArgs e)
        {
            DataRow comm;
            foreach (DataRow r in dataSetDbUtils.ResMan.Rows)
            {
                if (r["NAZWA"].IsNull())
                {
                    comm = dataSetDbUtils.USER_TAB_COLS.SelectFirstRow("COLUMN_NAME = " + r["KOD"].AsString().EscapeSql());
                    if (comm != null)
                        r["NAZWA"] = comm["COMMENTS"];
                    else
                    {
                        comm = dataSetDbUtils.USER_TABLES.SelectFirstRow("TABLE_NAME = " + r["KOD"].AsString().EscapeSql());
                        if (comm != null)
                            r["NAZWA"] = comm["COMMENTS"];
                    }
                }
            }
        }

        #endregion
        private void GetDBData(DataSets.DataSetDbUtils ds, DataRow dbRow)
        {
            ds.USER_TAB_COLS.Clear();
            ds.REFERENCES.Clear();
            ds.USER_TABLES.Clear();

            ProgNetComponentsDx.Forms.FormBackgroundWorkMultiThread f = new ProgNetComponentsDx.Forms.FormBackgroundWorkMultiThread("Pobieranie danych");
            f.AddAction(new Action<DataSets.DataSetDbUtils, DataRow>(getTables), ds, dbRow);
            f.AddAction(new Action<DataSets.DataSetDbUtils, DataRow>(getColumns), ds, dbRow);
            f.AddAction(new Action<DataSets.DataSetDbUtils, DataRow>(getReferences), ds, dbRow);
            
            f.BackgroundControl.SupportCancelation = false;
            f.BackgroundControl.ProgressType = ProgNetComponentsDx.Forms.ProgressType.Merque;
            f.ShowDialog();
        }
        private void GetDBDataForTool(DataSets.DataSetDbUtils ds, DataRow toolDbRow)
        {
            ds.GS_USER_CONSTRAINTS.Clear();

            ProgNetComponentsDx.Forms.FormBackgroundWorkMultiThread f = new ProgNetComponentsDx.Forms.FormBackgroundWorkMultiThread("Pobieranie danych");
            f.AddAction(new Action<DataSets.DataSetDbUtils, DataRow>(getUserConstraints), ds, toolDbRow);
           
            f.BackgroundControl.SupportCancelation = false;
            f.BackgroundControl.ProgressType = ProgNetComponentsDx.Forms.ProgressType.Merque;
            f.ShowDialog();
        }
        private void getColumns(DataSets.DataSetDbUtils ds, DataRow r)
        {
            string host = r["STP_BD_HOST"].AsString();
            string sid = r["STP_BD_SID"].AsString();
            string service = r["STP_BD_SERVICE"].AsString();
            string port = r["STP_BD_PORT"].AsString();
            string user = r["STP_BD_USER"].AsString();
            string pass = r["STP_BD_PASS"].AsString();

            DataSet ds2 = ds.Clone();
            ds2.EnforceConstraints = false;

            string connectionString = "Data Source=(DESCRIPTION=(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = " + host +
                ")(PORT = " + port + ")))(CONNECT_DATA = (SERVER=DEDICATED)(" +
                (service.IsNotNull() ? ("SERVICE_NAME = " + service) :
                ("SID = " + sid)) +
                ")));User Id=" + user + ";Password=" + pass + ";";

            string command = meKolumnySelect.Text.AsString();
            command = command.Replace("\r\n", " ");
            command = command.TrimEnd(';');
            System.Data.OracleClient.OracleDataAdapter a = new System.Data.OracleClient.OracleDataAdapter(command, connectionString);
            try
            {
                a.Fill(ds2.Tables["USER_TAB_COLS"]);
            }
            catch { }
            finally
            {
                try
                {
                    a.Dispose();
                }
                catch { }
            }
            Merge(ds, ds2);
        }
        
        private void getTables(DataSets.DataSetDbUtils ds, DataRow r)
        {
            string host = r["STP_BD_HOST"].AsString();
            string sid = r["STP_BD_SID"].AsString();
            string service = r["STP_BD_SERVICE"].AsString();
            string port = r["STP_BD_PORT"].AsString();
            string user = r["STP_BD_USER"].AsString();
            string pass = r["STP_BD_PASS"].AsString();

            DataSet ds2 = ds.Clone();
            ds2.EnforceConstraints = false;
            
            string connectionString = "Data Source=(DESCRIPTION=(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = " + host +
                ")(PORT = " + port + ")))(CONNECT_DATA = (SERVER=DEDICATED)(" +
                (service.IsNotNull() ? ("SERVICE_NAME = " + service) :
                ("SID = " + sid)) +
                ")));User Id=" + user + ";Password=" + pass + ";";

            string command = meTabeleSelect.Text.AsString();
            command = command.Replace("\r\n", " ");
            command = command.TrimEnd(';');
            System.Data.OracleClient.OracleDataAdapter a = new System.Data.OracleClient.OracleDataAdapter(command, connectionString);
            try
            {
                a.Fill(ds2.Tables["USER_TABLES"]);
            }
            catch { }
            finally
            {
                try
                {
                    a.Dispose();
                }
                catch { }
            }
            
            Merge(ds, ds2);
        }
        private void getReferences(DataSets.DataSetDbUtils ds, DataRow r)
        {
            string host = r["STP_BD_HOST"].AsString();
            string sid = r["STP_BD_SID"].AsString();
            string service = r["STP_BD_SERVICE"].AsString();
            string port = r["STP_BD_PORT"].AsString();
            string user = r["STP_BD_USER"].AsString();
            string pass = r["STP_BD_PASS"].AsString();

            DataSet ds2 = ds.Clone();

            string connectionString = "Data Source=(DESCRIPTION=(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = " + host +
                ")(PORT = " + port + ")))(CONNECT_DATA = (SERVER=DEDICATED)(" +
                (service.IsNotNull() ? ("SERVICE_NAME = " + service) :
                ("SID = " + sid)) +
                ")));User Id=" + user + ";Password=" + pass + ";";

            string command = meReferencesSelect.Text.AsString();
            command = command.Replace("\r\n", " ");
            command = command.TrimEnd(';');
            System.Data.OracleClient.OracleDataAdapter a = new System.Data.OracleClient.OracleDataAdapter(command, connectionString);
            try
            {
                a.Fill(ds2.Tables["REFERENCES"]);
            }
            catch { }
            
            Merge(ds, ds2);
        }
        private void getUserConstraints(DataSets.DataSetDbUtils ds, DataRow r)
        {
            string host = r["STP_BD_HOST"].AsString();
            string sid = r["STP_BD_SID"].AsString();
            string service = r["STP_BD_SERVICE"].AsString();
            string port = r["STP_BD_PORT"].AsString();
            string user = r["STP_BD_USER"].AsString();
            string pass = r["STP_BD_PASS"].AsString();

            DataSet ds2 = ds.Clone();
            ds2.EnforceConstraints = false;

            string connectionString = "Data Source=(DESCRIPTION=(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = " + host +
                ")(PORT = " + port + ")))(CONNECT_DATA = (SERVER=DEDICATED)(" +
                (service.IsNotNull() ? ("SERVICE_NAME = " + service) :
                ("SID = " + sid)) +
                ")));User Id=" + user + ";Password=" + pass + ";";

            string command = meUserConstraintsSelect.Text.AsString();
            command = command.Replace("\r\n", " ");
            command = command.TrimEnd(';');
            System.Data.OracleClient.OracleDataAdapter a = new System.Data.OracleClient.OracleDataAdapter(command, connectionString);
            try
            {
                a.Fill(ds2.Tables["GS_USER_CONSTRAINTS"]);
            }
            catch { }
            finally
            {
                try
                {
                    a.Dispose();
                }
                catch { }
            }
            List<string> l = new List<string>();
            foreach (DataRow dr in ds2.Tables["USER_TAB_COLS"].Rows)
            {
                if (!l.Contains(dr["TABLE_NAME"].AsString()))
                {
                    ds2.Tables["USER_TABLES"].Rows.Add(dr["TABLE_NAME"].AsString());
                    l.Add(dr["TABLE_NAME"].AsString());
                }
            }
            Merge(ds, ds2);
        }
        private void Merge(DataSet ds1, DataSet ds2)
        {
            if (this.InvokeRequired)
            {
                this.Invoke(new Action<DataSet,DataSet>(Merge), ds1,ds2);
                return;
            }
            ds1.Merge(ds2);
        }
        private void btnGetDbData_Click(object sender, EventArgs e)
        {
            if (lueDB.EditValue.IsNull())
            {
                this.ShowDxInfoDialog("Wybierz bazę");
                return;
            }
           
            DataRow dbRow = browserProvider1.GetRowForEditValue(lueDB);
            if (dbRow == null)
            {
                this.ShowDxErrorDialog("Nie można odnaleźć bazy");
                return;
            }
           
            GetDBData(this.dataSetDbUtils, dbRow);
        }
        private bool isChildRel(DataRow rel)
        {
            DataRow r = dataSetDbUtils.GS_USER_CONSTRAINTS.SelectFirstRow("GS_USERC_TYPE = 'C' and GS_USERC_TABLE = " + rel["TABLE_NAME"].AsString().EscapeSql());
            if (r != null)
            {
                string cols = r["GS_USERC_UNQCOLUMNS"].AsString().XReplace(" ", "");
                if (cols.Split(',').Contains(rel["COLUMN_NAME"].AsString()))
                    return true;
                return false;
            }
            DataRow[] rows =  dataSetDbUtils.USER_TAB_COLS.Select("TABLE_NAME = " + rel["TABLE_NAME"].AsString().EscapeSql(), "COLUMN_ID");
            bool recid = false;
            foreach (DataRow r2 in rows)
            {
                if (recid)
                {
                    return r2["COLUMN_NAME"].AsString() == rel["COLUMN_NAME"].AsString();
                }
                if (r2["COLUMN_NAME"].AsString().EndsWith("_RECID"))
                {
                    recid = true;
                }
            }
            return false;
        }

        private void btnGetDbDataForTool_Click(object sender, EventArgs e)
        {
            if (lueDBForTool.EditValue.IsNull())
            {
                this.ShowDxInfoDialog("Wybierz bazę dla toola");
                return;
            }
            DataRow dbRowForTool = browserProvider1.GetRowForEditValue(lueDBForTool);
            if (dbRowForTool == null)
            {
                this.ShowDxErrorDialog("Nie można odnaleźć bazy dla toola");
                return;
            }
            GetDBDataForTool(this.dataSetDbUtils, dbRowForTool);
        }

        private void GetAllData(DataSets.DataSetDbUtils ds, DataRow dbRow, DataRow toolDbRow)
        {
            ds.USER_TAB_COLS.Clear();
            ds.REFERENCES.Clear();
            ds.USER_TABLES.Clear();
            ds.GS_USER_CONSTRAINTS.Clear();

            ProgNetComponentsDx.Forms.FormBackgroundWorkMultiThread f = new ProgNetComponentsDx.Forms.FormBackgroundWorkMultiThread("Pobieranie danych");

            f.AddAction(new Action<DataSets.DataSetDbUtils, DataRow>(getTables), ds, dbRow);
            f.AddAction(new Action<DataSets.DataSetDbUtils, DataRow>(getColumns), ds, dbRow);
            f.AddAction(new Action<DataSets.DataSetDbUtils, DataRow>(getReferences), ds, dbRow);
            f.AddAction(new Action<DataSets.DataSetDbUtils, DataRow>(getUserConstraints), ds, toolDbRow);

            f.BackgroundControl.SupportCancelation = false;
            f.BackgroundControl.ProgressType = ProgNetComponentsDx.Forms.ProgressType.Merque;
            f.ShowDialog();
        }

        private void btnGetAll_Click(object sender, EventArgs e)
        {
            if (lueDB.EditValue.IsNull())
            {
                this.ShowDxInfoDialog("Wybierz bazę");
                return;
            }

            DataRow dbRow = browserProvider1.GetRowForEditValue(lueDB);
            if (dbRow == null)
            {
                this.ShowDxErrorDialog("Nie można odnaleźć bazy");
                return;
            }

            if (lueDBForTool.EditValue.IsNull())
            {
                this.ShowDxInfoDialog("Wybierz bazę dla toola");
                return;
            }
            DataRow dbRowForTool = browserProvider1.GetRowForEditValue(lueDBForTool);
            if (dbRowForTool == null)
            {
                this.ShowDxErrorDialog("Nie można odnaleźć bazy dla toola");
                return;
            }

            GetAllData(dataSetDbUtils, dbRow, dbRowForTool);
        }
    }
}
